Imports System.Data
Imports System.Data.SqlClient

Module modConsecutivos

   Public Function NumRegional() As String
      Dim cnConn As New SqlConnection
      Dim strRetVal As String

      Try

         cnConn.ConnectionString = gstrConnection
         cnConn.Open()

         Dim strConsecutivo As String
         Dim intConsecutivo As Integer

         strConsecutivo = BuscarStrDato("pvSetup", "Consecutivo", "SetupID = 'PV'")

         If strConsecutivo = "" Then
            strConsecutivo = "0"
         End If

         intConsecutivo = CInt(strConsecutivo) + 1

         strRetVal = Format(intConsecutivo, "00")

         Dim cmdRegional As New SqlCommand
         cmdRegional.Connection = cnConn
         cmdRegional.CommandText = "Update pvSetup Set Consecutivo = '" & strRetVal & "' Where SetupId = 'PV'"

         cmdRegional.ExecuteNonQuery()

      Catch ex As Exception

      Finally
         If cnConn.State = ConnectionState.Open Then
            cnConn.Close()
         End If
      End Try

      Return strRetVal

   End Function

   Public Function NumSucursal(ByVal strRegional As String) As String
      Dim cnConn As New SqlConnection
      Dim strRetVal As String

      Try

         cnConn.ConnectionString = gstrConnection
         cnConn.Open()

         Dim strConsecutivo As String
         Dim intConsecutivo As Integer

         strConsecutivo = BuscarStrDato("pvRegionales", "NumSucursal", "Regional = '" & strRegional & "'")

         If strConsecutivo = "" Then
            strConsecutivo = "0"
         End If

         intConsecutivo = CInt(strConsecutivo) + 1

         strRetVal = Format(intConsecutivo, "00")

         Dim cmdRegional As New SqlCommand
         cmdRegional.Connection = cnConn
         cmdRegional.CommandText = "Update pvRegionales Set NumSucursal = '" & strRetVal & "' Where Regional = '" & strRegional & "'"

         cmdRegional.ExecuteNonQuery()

      Catch ex As Exception

      Finally
         If cnConn.State = ConnectionState.Open Then
            cnConn.Close()
         End If
      End Try

      Return strRetVal

   End Function

   Public Function NumCaja(ByVal strRegional As String, ByVal strSucursal As String) As String
      Dim cnConn As New SqlConnection
      Dim strRetVal As String

      Try

         cnConn.ConnectionString = gstrConnection
         cnConn.Open()

         Dim strConsecutivo As String
         Dim intConsecutivo As Integer

         strConsecutivo = BuscarStrDato("pvSucursales", "NumCaja", "Regional = '" & strRegional & "' And Sucursal = '" & strSucursal & "'")

         If strConsecutivo = "" Then
            strConsecutivo = "0"
         End If

         intConsecutivo = CInt(strConsecutivo) + 1

         strRetVal = Format(intConsecutivo, "00")

         Dim cmdRegional As New SqlCommand
         cmdRegional.Connection = cnConn
         cmdRegional.CommandText = "Update pvSucursales Set NumCaja = '" & strRetVal & "' Where Regional = '" & strRegional & "' And Sucursal = '" & strSucursal & "'"

         cmdRegional.ExecuteNonQuery()

      Catch ex As Exception

      Finally
         If cnConn.State = ConnectionState.Open Then
            cnConn.Close()
         End If
      End Try

      Return strRetVal

   End Function
   Public Function NumNotaVenta(ByVal strRegional As String, ByVal strSucursal As String, ByVal strCaja As String, ByVal cnConn As SqlConnection, ByVal trTran As SqlTransaction) As String
        Dim strRetVal As String
        Dim Folio As String
        Dim Serie As String

      Try

            Dim strConsecutivo As String
            Dim intConsecutivo As Integer

            Dim strLimiteInferior1 As String
            Dim intLimiteInferior1 As Integer
            Dim strLimiteSuperior1 As String
            Dim intLimiteSuperior1 As Integer

            Dim strLimiteInferior2 As String
            Dim intLimiteInferior2 As Integer
            Dim strLimiteSuperior2 As String
            Dim intLimiteSuperior2 As Integer

            Dim bolFacturaElectronica As Boolean
            Dim intLimitSuperiorActivo As Integer
            Dim idSiguienteFolioGenerado As Integer

            bolFacturaElectronica = BuscarIntDato("pvCajas", "facturaelectronica", "Regional = '" & strRegional & "' And Sucursal = '" & strSucursal & "' And Caja = '" & strCaja & "'")

            If bolFacturaElectronica Then
                Serie = BuscarStrDato("pvCajas", "Serie", "Regional = '" & strRegional & "' And Sucursal = '" & strSucursal & "' And Caja = '" & strCaja & "'")
                strConsecutivo = BuscarStrDato("pvCajas", "NumNotaE", "Regional = '" & strRegional & "' And Sucursal = '" & strSucursal & "' And Caja = '" & strCaja & "'")
                If strConsecutivo = "" Then
                    strConsecutivo = "0"
                Else
                    intConsecutivo = CInt(strConsecutivo)
                    intLimitSuperiorActivo = BuscarIntDato("pvFolios", "folio_final", "Regional = '" & strRegional & "' And Sucursal = '" & strSucursal & "' And Caja = '" & strCaja & "' and Status = 'A'")
                    If intConsecutivo >= intLimitSuperiorActivo Then
                        idSiguienteFolioGenerado = BuscarIntDato("pvFolios", "id", "Regional = '" & strRegional & "' And Sucursal = '" & strSucursal & "' And Caja = '" & strCaja & "' and Status = 'G' and fecha = (select max(fecha) from pvFolios where Regional = '" & strRegional & "' And Sucursal = '" & strSucursal & "' And Caja = '" & strCaja & "' and Status = 'G')")

                        intConsecutivo = BuscarIntDato("pvFolios", "folio_inicial", "Regional = '" & strRegional & "' And Sucursal = '" & strSucursal & "' And Caja = '" & strCaja & "' and id = " & idSiguienteFolioGenerado)

                        Dim cmdUpdateFolioActivo As New SqlCommand
                        cmdUpdateFolioActivo.Connection = cnConn
                        cmdUpdateFolioActivo.Transaction = trTran
                        cmdUpdateFolioActivo.CommandText = "Update pvFolios Set status = 'I' Regional = '" & strRegional & "' And Sucursal = '" & strSucursal & "' And Caja = '" & strCaja & "' and Status = 'A'"
                        cmdUpdateFolioActivo.ExecuteNonQuery()

                        Dim cmdUpdateFolioGenerado As New SqlCommand
                        cmdUpdateFolioGenerado.Connection = cnConn
                        cmdUpdateFolioGenerado.Transaction = trTran
                        cmdUpdateFolioGenerado.CommandText = "Update pvFolios Set status = 'A' Regional = '" & strRegional & "' And Sucursal = '" & strSucursal & "' And Caja = '" & strCaja & "' and id = " & idSiguienteFolioGenerado
                        cmdUpdateFolioGenerado.ExecuteNonQuery()
                    Else
                        intConsecutivo = intConsecutivo + 1
                    End If

                    Dim cmdRegional As New SqlCommand
                    cmdRegional.Connection = cnConn
                    cmdRegional.Transaction = trTran
                    cmdRegional.CommandText = "Update pvCajas Set NumNotaE = '" & intConsecutivo & "' Where Regional = '" & strRegional & "' And Sucursal = '" & strSucursal & "' And Caja = '" & strCaja & "'"

                    cmdRegional.ExecuteNonQuery()
                End If
            Else
                strConsecutivo = BuscarStrDato("pvCajas", "NumNota", "Regional = '" & strRegional & "' And Sucursal = '" & strSucursal & "' And Caja = '" & strCaja & "'")
                strLimiteInferior1 = BuscarStrDato("pvCajas", "NumNotaIniR1", "Regional = '" & strRegional & "' And Sucursal = '" & strSucursal & "' And Caja = '" & strCaja & "'")
                strLimiteSuperior1 = BuscarStrDato("pvCajas", "NumNotaFinR1", "Regional = '" & strRegional & "' And Sucursal = '" & strSucursal & "' And Caja = '" & strCaja & "'")

                strLimiteInferior2 = BuscarStrDato("pvCajas", "NumNotaIniR2", "Regional = '" & strRegional & "' And Sucursal = '" & strSucursal & "' And Caja = '" & strCaja & "'")
                strLimiteSuperior2 = BuscarStrDato("pvCajas", "NumNotaFinR2", "Regional = '" & strRegional & "' And Sucursal = '" & strSucursal & "' And Caja = '" & strCaja & "'")

                If strConsecutivo = "" Then
                    strConsecutivo = "0"
                End If

                intConsecutivo = CInt(strConsecutivo)

                If strLimiteInferior1 <> "" And strLimiteSuperior1 <> "" And strLimiteInferior2 <> "" And strLimiteSuperior2 <> "" Then
                    intLimiteInferior1 = CInt(strLimiteInferior1)
                    intLimiteSuperior1 = CInt(strLimiteSuperior1)
                    intLimiteInferior2 = CInt(strLimiteInferior2)
                    intLimiteSuperior2 = CInt(strLimiteSuperior2)

                    If intConsecutivo = intLimiteSuperior1 Then
                        intConsecutivo = intLimiteInferior2
                        ActualizarConsecutivosSucursal(strRegional, strSucursal, strCaja, cnConn, trTran, 1)
                    Else
                        If intConsecutivo = intLimiteSuperior2 Then
                            intConsecutivo = intLimiteInferior1
                            ActualizarConsecutivosSucursal(strRegional, strSucursal, strCaja, cnConn, trTran, 2)
                        Else
                            intConsecutivo = intConsecutivo + 1
                        End If
                    End If
                Else
                    Return ""
                End If

                Dim cmdRegional As New SqlCommand
                cmdRegional.Connection = cnConn
                cmdRegional.Transaction = trTran
                cmdRegional.CommandText = "Update pvCajas Set NumNota = '" & intConsecutivo & "' Where Regional = '" & strRegional & "' And Sucursal = '" & strSucursal & "' And Caja = '" & strCaja & "'"

                cmdRegional.ExecuteNonQuery()
            End If

            If bolFacturaElectronica Then
                strRetVal = Format(intConsecutivo, "0000000000")
                strRetVal = strRetVal.Substring(strRetVal.Length - 10)
                'Folio = Serie & strRetVal
                Folio = strRetVal
            Else
                strRetVal = Format(intConsecutivo, "000000")
                strRetVal = strRetVal.Substring(strRetVal.Length - 6)
                Folio = gstrRegional & gstrSucursal & strRetVal
            End If

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

        Return Folio

    End Function

    Private Sub ActualizarConsecutivosSucursal(ByVal strRegional As String, ByVal strSucursal As String, ByVal strCaja As String, ByVal cnConn As SqlConnection, ByVal trTran As SqlTransaction, ByVal int_numRango As Integer)
        Dim cmdRegional As New SqlCommand
        cmdRegional.Connection = cnConn
        cmdRegional.Transaction = trTran
        cmdRegional.CommandText = " Update pvCajas " & _
                                  " Set    NumNotaIniR" & int_numRango & " = '0000000000', " & _
                                  "        NumNotaFinR" & int_numRango & " = '0000000001' " & _
                                  " Where  Regional = '" & strRegional & "' And " & _
                                  "        Sucursal = '" & strSucursal & "' And " & _
                                  "        Caja = '" & strCaja & "'"

        cmdRegional.ExecuteNonQuery()
    End Sub

    Public Function NumSolDes() As String
        Dim cnConn As New SqlConnection
        Dim strRetVal As String

        Try

            cnConn.ConnectionString = gstrConnection
            cnConn.Open()

            Dim strConsecutivo As String
            Dim intConsecutivo As Integer

            strConsecutivo = BuscarStrDato("pvSucursales", "NumSolDes", "Regional = '" & gstrRegional & "' And Sucursal = '" & gstrSucursal & "'")

            If strConsecutivo = "" Then
                strConsecutivo = "0"
            End If

            intConsecutivo = CInt(strConsecutivo) + 1

            strRetVal = Format(intConsecutivo, "000000")

            strRetVal = strRetVal.Substring(strRetVal.Length - 6)

            Dim cmdRegional As New SqlCommand
            cmdRegional.Connection = cnConn
            cmdRegional.CommandText = "Update pvSucursales Set NumSolDes = '" & strRetVal & "' Where Regional = '" & gstrRegional & "' And Sucursal = '" & gstrSucursal & "'"

            cmdRegional.ExecuteNonQuery()

        Catch ex As Exception

        Finally
            If cnConn.State = ConnectionState.Open Then
                cnConn.Close()
            End If
        End Try

        Return gstrRegional & gstrSucursal & strRetVal

    End Function

    Public Function NumSolDesObsDa() As String
        Dim cnConn As New SqlConnection
        Dim strRetVal As String

        Try

            cnConn.ConnectionString = gstrConnection
            cnConn.Open()

            Dim strConsecutivo As String
            Dim intConsecutivo As Integer

            strConsecutivo = BuscarStrDato("pvSucursales", "NumSolDesObsDa", "Regional = '" & gstrRegional & "' And Sucursal = '" & gstrSucursal & "'")

            If strConsecutivo = "" Then
                strConsecutivo = "0"
            End If

            intConsecutivo = CInt(strConsecutivo) + 1

            strRetVal = Format(intConsecutivo, "000000")

            strRetVal = strRetVal.Substring(strRetVal.Length - 6)

            Dim cmdRegional As New SqlCommand
            cmdRegional.Connection = cnConn
            cmdRegional.CommandText = "Update pvSucursales Set NumSolDesObsDa = '" & strRetVal & "' Where Regional = '" & gstrRegional & "' And Sucursal = '" & gstrSucursal & "'"

            cmdRegional.ExecuteNonQuery()

        Catch ex As Exception

        Finally
            If cnConn.State = ConnectionState.Open Then
                cnConn.Close()
            End If
        End Try

        Return gstrRegional & gstrSucursal & strRetVal

    End Function

    Public Function NumAutDes(ByVal strRegional As String, ByVal strSucursal As String) As String
        Dim cnConn As New SqlConnection
        Dim strRetVal As String

        Try

            cnConn.ConnectionString = gstrConnection
            cnConn.Open()

            Dim strConsecutivo As String
            Dim intConsecutivo As Integer

            strConsecutivo = BuscarStrDato("pvSucursales", "NumAutDes", "Regional = '" & strRegional & "' And Sucursal = '" & strSucursal & "'")

            If strConsecutivo = "" Then
                strConsecutivo = "0"
            End If

            intConsecutivo = CInt(strConsecutivo) + 1

            strRetVal = Format(intConsecutivo, "000000")

            strRetVal = strRetVal.Substring(strRetVal.Length - 6)

            Dim cmdRegional As New SqlCommand
            cmdRegional.Connection = cnConn
            cmdRegional.CommandText = "Update pvSucursales Set NumAutDes = '" & strRetVal & "' Where Regional = '" & strRegional & "' And Sucursal = '" & strSucursal & "'"

            cmdRegional.ExecuteNonQuery()

        Catch ex As Exception

        Finally
            If cnConn.State = ConnectionState.Open Then
                cnConn.Close()
            End If
        End Try

        Return strRegional & strSucursal & strRetVal

    End Function

    Public Function NumApartado(ByVal strRegional As String, ByVal strSucursal As String) As String
        Dim cnConn As New SqlConnection
        Dim strRetVal As String

        Try

            cnConn.ConnectionString = gstrConnection
            cnConn.Open()

            Dim strConsecutivo As String
            Dim intConsecutivo As Integer

            Dim strLimiteInferior1 As String
            Dim intLimiteInferior1 As Integer
            Dim strLimiteSuperior1 As String
            Dim intLimiteSuperior1 As Integer

            Dim strLimiteInferior2 As String
            Dim intLimiteInferior2 As Integer
            Dim strLimiteSuperior2 As String
            Dim intLimiteSuperior2 As Integer

            strConsecutivo = BuscarStrDato("pvSucursales", "NumApart", "Regional = '" & strRegional & "' And Sucursal = '" & strSucursal & "'")

            strLimiteInferior1 = BuscarStrDato("pvSucursales", "NumNotaIniR1", "Regional = '" & strRegional & "' And Sucursal = '" & strSucursal & "'")
            strLimiteSuperior1 = BuscarStrDato("pvSucursales", "NumNotaFinR1", "Regional = '" & strRegional & "' And Sucursal = '" & strSucursal & "'")

            strLimiteInferior2 = BuscarStrDato("pvSucursales", "NumNotaIniR2", "Regional = '" & strRegional & "' And Sucursal = '" & strSucursal & "'")
            strLimiteSuperior2 = BuscarStrDato("pvSucursales", "NumNotaFinR2", "Regional = '" & strRegional & "' And Sucursal = '" & strSucursal & "'")

            If strConsecutivo = "" Then
                strConsecutivo = "0"
            End If

            intConsecutivo = CInt(strConsecutivo)

            If strLimiteInferior1 <> "" And strLimiteSuperior1 <> "" And strLimiteInferior2 <> "" And strLimiteSuperior2 <> "" Then
                intLimiteInferior1 = CInt(strLimiteInferior1)
                intLimiteSuperior1 = CInt(strLimiteSuperior1)
                intLimiteInferior2 = CInt(strLimiteInferior2)
                intLimiteSuperior2 = CInt(strLimiteSuperior2)

                If intConsecutivo = intLimiteSuperior1 Then
                    intConsecutivo = intLimiteInferior2
                Else
                    If intConsecutivo = intLimiteSuperior2 Then
                        intConsecutivo = intLimiteSuperior1
                    End If
                End If
            End If

            intConsecutivo = intConsecutivo + 1

            strRetVal = Format(intConsecutivo, "000000")

            strRetVal = strRetVal.Substring(strRetVal.Length - 6)

            Dim cmdRegional As New SqlCommand
            cmdRegional.Connection = cnConn
            cmdRegional.CommandText = "Update pvSucursales Set NumApart = '" & strRetVal & "' Where Regional = '" & strRegional & "' And Sucursal = '" & strSucursal & "'"

            cmdRegional.ExecuteNonQuery()

        Catch ex As Exception

        Finally
            If cnConn.State = ConnectionState.Open Then
                cnConn.Close()
            End If
        End Try

        Return gstrRegional & gstrSucursal & strRetVal

    End Function

    Public Function NumCancelacionNotaVenta() As String
        Dim strRetVal As String

        Dim cnConn As New SqlConnection

        Try

            cnConn.ConnectionString = gstrConnection
            cnConn.Open()

            Dim strConsecutivo As String
            Dim intConsecutivo As Integer

            strConsecutivo = BuscarStrDato("pvSucursales", "FolDevCan", "Regional = '" & gstrRegional & "' And Sucursal = '" & gstrSucursal & "'")

            If strConsecutivo = "" Then
                strConsecutivo = "0"
            End If

            intConsecutivo = CInt(strConsecutivo) + 1

            strRetVal = Format(intConsecutivo, "000000")

            strRetVal = strRetVal.Substring(strRetVal.Length - 6)

            Dim cmdRegional As New SqlCommand
            cmdRegional.Connection = cnConn

            cmdRegional.CommandText = "Update pvSucursales Set FolDevCan = '" & strRetVal & "' Where Regional = '" & gstrRegional & "' And Sucursal = '" & gstrSucursal & "'"

            cmdRegional.ExecuteNonQuery()

        Catch ex As Exception
            Return ""

        Finally
            If cnConn.State = ConnectionState.Open Then
                cnConn.Close()
            End If
        End Try

        Return gstrRegional & gstrSucursal & strRetVal

    End Function

    Public Function NumRequerimiento() As String
        Dim cnConn As New SqlConnection
        Dim strRetVal As String

        Try

            cnConn.ConnectionString = gstrConnection
            cnConn.Open()

            Dim strConsecutivo As String
            Dim intConsecutivo As Integer

            strConsecutivo = BuscarStrDato("pvSucursales", "NumRequer", "Regional = '" & gstrRegional & "' And Sucursal = '" & gstrSucursal & "'")

            If strConsecutivo = "" Then
                strConsecutivo = "0"
            End If

            intConsecutivo = CInt(strConsecutivo) + 1

            strRetVal = Format(intConsecutivo, "000000")

            strRetVal = strRetVal.Substring(strRetVal.Length - 6)

            Dim cmdRegional As New SqlCommand
            cmdRegional.Connection = cnConn
            cmdRegional.CommandText = "Update pvSucursales Set NumRequer = '" & strRetVal & "' Where Regional = '" & gstrRegional & "' And Sucursal = '" & gstrSucursal & "'"

            cmdRegional.ExecuteNonQuery()

        Catch ex As Exception

        Finally
            If cnConn.State = ConnectionState.Open Then
                cnConn.Close()
            End If
        End Try

        Return gstrRegional & gstrSucursal & strRetVal

    End Function

    Public Function NumTraspaso() As String
        Dim cnConn As New SqlConnection
        Dim strRetVal As String

        Try

            cnConn.ConnectionString = gstrConnection
            cnConn.Open()

            Dim strConsecutivo As String
            Dim intConsecutivo As Integer

            strConsecutivo = BuscarStrDato("pvSucursales", "NumTraspa", "Regional = '" & gstrRegional & "' And Sucursal = '" & gstrSucursal & "'")

            If strConsecutivo = "" Then
                strConsecutivo = "0"
            End If

            intConsecutivo = CInt(strConsecutivo) + 1

            strRetVal = Format(intConsecutivo, "000000")

            strRetVal = strRetVal.Substring(strRetVal.Length - 6)

            Dim cmdRegional As New SqlCommand
            cmdRegional.Connection = cnConn
            cmdRegional.CommandText = "Update pvSucursales Set NumTraspa = '" & strRetVal & "' Where Regional = '" & gstrRegional & "' And Sucursal = '" & gstrSucursal & "'"

            cmdRegional.ExecuteNonQuery()

        Catch ex As Exception

        Finally
            If cnConn.State = ConnectionState.Open Then
                cnConn.Close()
            End If
        End Try

        Return gstrRegional & gstrSucursal & strRetVal

    End Function

    Public Function NumSalida(ByVal cnConn As SqlConnection, ByVal trTran As SqlTransaction) As String
        Dim strRetVal As String

        Try

            Dim strConsecutivo As String
            Dim intConsecutivo As Integer

            strConsecutivo = BuscarStrDato("INSetup", "LastBatNbr", "SetupId = 'IN'")

            If strConsecutivo = "" Then
                Throw New Exception("Error en el Modulo de Inventarios, Intente de Nuevo")
            End If

            intConsecutivo = CInt(strConsecutivo) + 1

            strRetVal = Format(intConsecutivo, "000000")

            strRetVal = strRetVal.Substring(strRetVal.Length - 6)

            Dim cmdRegional As New SqlCommand
            cmdRegional.Connection = cnConn
            cmdRegional.Transaction = trTran
            cmdRegional.CommandText = "Update INSetup Set LastBatNbr = '" & strRetVal & "'"

            cmdRegional.ExecuteNonQuery()

        Catch ex As Exception
            MessageBox.Show(ex.Message, "System Message", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Return ""
        End Try

        Return strRetVal

    End Function

    Public Function NumGarantia(ByVal strRegional As String, ByVal strSucursal As String) As String
        Dim cnConn As New SqlConnection
        Dim strRetVal As String

        Try

            cnConn.ConnectionString = gstrConnection
            cnConn.Open()

            Dim strConsecutivo As String
            Dim intConsecutivo As Integer

            strConsecutivo = BuscarStrDato("pvSucursales", "NumGaran", "Regional = '" & strRegional & "' And Sucursal = '" & strSucursal & "'")

            If strConsecutivo = "" Then
                strConsecutivo = "0"
            End If

            intConsecutivo = CInt(strConsecutivo) + 1

            strRetVal = Format(intConsecutivo, "000000")

            strRetVal = strRetVal.Substring(strRetVal.Length - 6)

            Dim cmdRegional As New SqlCommand
            cmdRegional.Connection = cnConn
            cmdRegional.CommandText = "Update pvSucursales Set NumGaran = '" & strRetVal & "' Where Regional = '" & strRegional & "' And Sucursal = '" & strSucursal & "'"

            cmdRegional.ExecuteNonQuery()

        Catch ex As Exception

        Finally
            If cnConn.State = ConnectionState.Open Then
                cnConn.Close()
            End If
        End Try

        Return gstrRegional & gstrSucursal & strRetVal

    End Function

    Public Function NumObsoletoDa(ByVal strRegional As String, ByVal strSucursal As String) As String
        Dim cnConn As New SqlConnection
        Dim strRetVal As String

        Try

            cnConn.ConnectionString = gstrConnection
            cnConn.Open()

            Dim strConsecutivo As String
            Dim intConsecutivo As Integer

            strConsecutivo = BuscarStrDato("pvSucursales", "NumObsDa", "Regional = '" & strRegional & "' And Sucursal = '" & strSucursal & "'")

            If strConsecutivo = "" Then
                strConsecutivo = "0"
            End If

            intConsecutivo = CInt(strConsecutivo) + 1

            strRetVal = Format(intConsecutivo, "000000")

            strRetVal = strRetVal.Substring(strRetVal.Length - 6)

            Dim cmdRegional As New SqlCommand
            cmdRegional.Connection = cnConn
            cmdRegional.CommandText = "Update pvSucursales Set NumObsDa = '" & strRetVal & "' Where Regional = '" & strRegional & "' And Sucursal = '" & strSucursal & "'"

            cmdRegional.ExecuteNonQuery()

        Catch ex As Exception

        Finally
            If cnConn.State = ConnectionState.Open Then
                cnConn.Close()
            End If
        End Try

        Return gstrRegional & gstrSucursal & strRetVal

    End Function

End Module
